package khfx.dao.common.impl;

import java.util.ArrayList;
import java.util.List;

import khfx.dao.BasicDao;
import khfx.dao.common.GroupFiveclassifyDao;
import khfx.dao.db.SQLQuery;
import khfx.dao.db.Session;
import khfx.domain.common.FiveclassifyVo;

import org.springframework.stereotype.Component;
@Component("GroupFiveclassifyDao")
public class GroupFiveclassifyDaoImpl extends BasicDao implements GroupFiveclassifyDao{

	@SuppressWarnings("unchecked")
	public List<FiveclassifyVo> groupFiveclassify(Session session,Integer reportDate){
		List<FiveclassifyVo> list = new ArrayList<FiveclassifyVo>();
		StringBuffer sql = new StringBuffer();
		sql.append("select GROUPID,sum(LOANBALANCE1)AS LOANBALANCE1 , ");
		sql.append("sum(LOANBALANCE2) AS LOANBALANCE2 ,sum(LOANBALANCE3)AS LOANBALANCE3, ");
		sql.append("sum(LOANBALANCE4)AS LOANBALANCE4,sum(LOANBALANCE5)AS LOANBALANCE5 ");
		sql.append("from(select G.GROUPID,FIVECLASSIFY, ");
		sql.append("case when FIVECLASSIFY=1 then sum(L.LOANBALANCE) end as LOANBALANCE1, ");
		sql.append("case when FIVECLASSIFY=2 then sum(L.LOANBALANCE) end as LOANBALANCE2, ");
		sql.append("case when FIVECLASSIFY=3 then sum(L.LOANBALANCE) end as LOANBALANCE3, ");
		sql.append("case when FIVECLASSIFY=4 then sum(L.LOANBALANCE) end as LOANBALANCE4, ");
		sql.append("case when FIVECLASSIFY=5 then sum(L.LOANBALANCE) end as LOANBALANCE5 ");
		sql.append("from ");
		sql.append("(select * from C_GROUP where REPORTDATE= ? ) G inner join ");
		sql.append("(select * from C_CREDIT  where REPORTDATE= ? ) C ");
		sql.append("on G.BANKCODE = C.BANKCODE and G.CUSTOMERCODE = C.CUSTOMERCODE inner join ");
		sql.append("(select * from C_CREDIT_LOAN  where REPORTDATE= ? ) L ");
		sql.append("on L.CREDITID = C.CREDITID ");
		sql.append("group by  G.GROUPID, FIVECLASSIFY) group by GROUPID WITH UR ");
		String sqlStr = sql.toString();
		SQLQuery query = session.createSQLQuery(sqlStr);
		query.setParameter(1, reportDate);
		query.setParameter(2, reportDate); 
		query.setParameter(3, reportDate); 
		query.addEntity(FiveclassifyVo.class);
		list = (List<FiveclassifyVo>) query.executeQuery();
		return list;
	}
}
